{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import pandas as pd\n",
    "sys.path.append('..')\n",
    "from configure.settings import DBSelector\n",
    "db = DBSelector()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = db.get_engine('db_stock','qq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql('tb_bond_jisilu',con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>可转债代码</th>\n",
       "      <th>可转债名称</th>\n",
       "      <th>可转债价格</th>\n",
       "      <th>正股名称</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>正股现价</th>\n",
       "      <th>正股涨跌幅</th>\n",
       "      <th>最新转股价</th>\n",
       "      <th>溢价率</th>\n",
       "      <th>可转债涨幅</th>\n",
       "      <th>...</th>\n",
       "      <th>下修提示</th>\n",
       "      <th>下调次数</th>\n",
       "      <th>转债剩余占总市值比</th>\n",
       "      <th>剩余规模</th>\n",
       "      <th>发行规模</th>\n",
       "      <th>股东配售率</th>\n",
       "      <th>发出强赎公告</th>\n",
       "      <th>强赎日期</th>\n",
       "      <th>担保</th>\n",
       "      <th>更新日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>127029</td>\n",
       "      <td>中钢转债</td>\n",
       "      <td>169.593</td>\n",
       "      <td>中钢国际</td>\n",
       "      <td>000928</td>\n",
       "      <td>11.02</td>\n",
       "      <td>-1.78</td>\n",
       "      <td>5.89</td>\n",
       "      <td>-9.36</td>\n",
       "      <td>-0.91</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>6.9</td>\n",
       "      <td>9.600</td>\n",
       "      <td>9.600</td>\n",
       "      <td>20.710</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>无</td>\n",
       "      <td>2021-05-26 15:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>113541</td>\n",
       "      <td>荣晟转债</td>\n",
       "      <td>132.370</td>\n",
       "      <td>荣晟环保</td>\n",
       "      <td>603165</td>\n",
       "      <td>14.80</td>\n",
       "      <td>8.90</td>\n",
       "      <td>10.84</td>\n",
       "      <td>-3.05</td>\n",
       "      <td>5.70</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>6.2</td>\n",
       "      <td>2.352</td>\n",
       "      <td>3.300</td>\n",
       "      <td>24.502</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>股份质押和保证</td>\n",
       "      <td>2021-05-26 15:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>128085</td>\n",
       "      <td>鸿达转债</td>\n",
       "      <td>107.240</td>\n",
       "      <td>鸿达兴业</td>\n",
       "      <td>002002</td>\n",
       "      <td>4.31</td>\n",
       "      <td>9.67</td>\n",
       "      <td>3.92</td>\n",
       "      <td>-2.46</td>\n",
       "      <td>5.92</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>8.8</td>\n",
       "      <td>11.067</td>\n",
       "      <td>24.270</td>\n",
       "      <td>26.280</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>无担保</td>\n",
       "      <td>2021-05-26 15:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>113527</td>\n",
       "      <td>维格转债</td>\n",
       "      <td>199.000</td>\n",
       "      <td>锦泓集团</td>\n",
       "      <td>603518</td>\n",
       "      <td>19.99</td>\n",
       "      <td>-2.11</td>\n",
       "      <td>9.85</td>\n",
       "      <td>-1.94</td>\n",
       "      <td>-3.38</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>8.7</td>\n",
       "      <td>4.775</td>\n",
       "      <td>7.460</td>\n",
       "      <td>4.910</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>无担保</td>\n",
       "      <td>2021-05-26 15:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>113559</td>\n",
       "      <td>永创转债</td>\n",
       "      <td>141.120</td>\n",
       "      <td>永创智能</td>\n",
       "      <td>603901</td>\n",
       "      <td>14.73</td>\n",
       "      <td>0.07</td>\n",
       "      <td>10.32</td>\n",
       "      <td>-1.13</td>\n",
       "      <td>0.08</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>4.143</td>\n",
       "      <td>5.120</td>\n",
       "      <td>57.120</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>股份质押和保证</td>\n",
       "      <td>2021-05-26 15:03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    可转债代码 可转债名称    可转债价格  正股名称    正股代码   正股现价  正股涨跌幅  最新转股价   溢价率  可转债涨幅  ...  \\\n",
       "0  127029  中钢转债  169.593  中钢国际  000928  11.02  -1.78   5.89 -9.36  -0.91  ...   \n",
       "1  113541  荣晟转债  132.370  荣晟环保  603165  14.80   8.90  10.84 -3.05   5.70  ...   \n",
       "2  128085  鸿达转债  107.240  鸿达兴业  002002   4.31   9.67   3.92 -2.46   5.92  ...   \n",
       "3  113527  维格转债  199.000  锦泓集团  603518  19.99  -2.11   9.85 -1.94  -3.38  ...   \n",
       "4  113559  永创转债  141.120  永创智能  603901  14.73   0.07  10.32 -1.13   0.08  ...   \n",
       "\n",
       "   下修提示 下调次数 转债剩余占总市值比    剩余规模    发行规模   股东配售率 发出强赎公告  强赎日期       担保  \\\n",
       "0          0       6.9   9.600   9.600  20.710      X  None        无   \n",
       "1          1       6.2   2.352   3.300  24.502      X  None  股份质押和保证   \n",
       "2          0       8.8  11.067  24.270  26.280      X  None      无担保   \n",
       "3          1       8.7   4.775   7.460   4.910      X  None      无担保   \n",
       "4          0       6.5   4.143   5.120  57.120      X  None  股份质押和保证   \n",
       "\n",
       "               更新日期  \n",
       "0  2021-05-26 15:03  \n",
       "1  2021-05-26 15:03  \n",
       "2  2021-05-26 15:03  \n",
       "3  2021-05-26 15:03  \n",
       "4  2021-05-26 15:03  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=pd.read_excel(r'D:\\Download\\roe_pe.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>bond</th>\n",
       "      <th>name</th>\n",
       "      <th>pe</th>\n",
       "      <th>roe</th>\n",
       "      <th>zg_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>110031</td>\n",
       "      <td>航信转债</td>\n",
       "      <td>13.5730</td>\n",
       "      <td>7.503275</td>\n",
       "      <td>600271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>110033</td>\n",
       "      <td>国贸转债</td>\n",
       "      <td>5.0221</td>\n",
       "      <td>6.168038</td>\n",
       "      <td>600755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>110034</td>\n",
       "      <td>九州转债</td>\n",
       "      <td>9.7541</td>\n",
       "      <td>4.559387</td>\n",
       "      <td>600998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>110038</td>\n",
       "      <td>济川转债</td>\n",
       "      <td>13.6487</td>\n",
       "      <td>19.987800</td>\n",
       "      <td>600566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>110041</td>\n",
       "      <td>蒙电转债</td>\n",
       "      <td>18.4247</td>\n",
       "      <td>4.442312</td>\n",
       "      <td>600863</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0    bond  name       pe        roe  zg_code\n",
       "0           0  110031  航信转债  13.5730   7.503275   600271\n",
       "1           1  110033  国贸转债   5.0221   6.168038   600755\n",
       "2           2  110034  九州转债   9.7541   4.559387   600998\n",
       "3           3  110038  济川转债  13.6487  19.987800   600566\n",
       "4           4  110041  蒙电转债  18.4247   4.442312   600863"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3=pd.merge(df,df2,left_on='可转债代码',right_on='bond')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['bond']=df2['bond'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>可转债代码</th>\n",
       "      <th>可转债名称</th>\n",
       "      <th>可转债价格</th>\n",
       "      <th>正股名称</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>正股现价</th>\n",
       "      <th>正股涨跌幅</th>\n",
       "      <th>最新转股价</th>\n",
       "      <th>溢价率</th>\n",
       "      <th>可转债涨幅</th>\n",
       "      <th>...</th>\n",
       "      <th>发出强赎公告</th>\n",
       "      <th>强赎日期</th>\n",
       "      <th>担保</th>\n",
       "      <th>更新日期</th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>bond</th>\n",
       "      <th>name</th>\n",
       "      <th>pe</th>\n",
       "      <th>roe</th>\n",
       "      <th>zg_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>127029</td>\n",
       "      <td>中钢转债</td>\n",
       "      <td>169.593</td>\n",
       "      <td>中钢国际</td>\n",
       "      <td>000928</td>\n",
       "      <td>11.02</td>\n",
       "      <td>-1.78</td>\n",
       "      <td>5.89</td>\n",
       "      <td>-9.36</td>\n",
       "      <td>-0.91</td>\n",
       "      <td>...</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>无</td>\n",
       "      <td>2021-05-26 15:03</td>\n",
       "      <td>260</td>\n",
       "      <td>127029</td>\n",
       "      <td>中钢转债</td>\n",
       "      <td>19.6973</td>\n",
       "      <td>6.024312</td>\n",
       "      <td>928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>113541</td>\n",
       "      <td>荣晟转债</td>\n",
       "      <td>132.370</td>\n",
       "      <td>荣晟环保</td>\n",
       "      <td>603165</td>\n",
       "      <td>14.80</td>\n",
       "      <td>8.90</td>\n",
       "      <td>10.84</td>\n",
       "      <td>-3.05</td>\n",
       "      <td>5.70</td>\n",
       "      <td>...</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>股份质押和保证</td>\n",
       "      <td>2021-05-26 15:03</td>\n",
       "      <td>81</td>\n",
       "      <td>113541</td>\n",
       "      <td>荣晟转债</td>\n",
       "      <td>13.8036</td>\n",
       "      <td>11.290425</td>\n",
       "      <td>603165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>128085</td>\n",
       "      <td>鸿达转债</td>\n",
       "      <td>107.240</td>\n",
       "      <td>鸿达兴业</td>\n",
       "      <td>002002</td>\n",
       "      <td>4.31</td>\n",
       "      <td>9.67</td>\n",
       "      <td>3.92</td>\n",
       "      <td>-2.46</td>\n",
       "      <td>5.92</td>\n",
       "      <td>...</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>无担保</td>\n",
       "      <td>2021-05-26 15:03</td>\n",
       "      <td>313</td>\n",
       "      <td>128085</td>\n",
       "      <td>鸿达转债</td>\n",
       "      <td>12.8037</td>\n",
       "      <td>6.940487</td>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>113527</td>\n",
       "      <td>维格转债</td>\n",
       "      <td>199.000</td>\n",
       "      <td>锦泓集团</td>\n",
       "      <td>603518</td>\n",
       "      <td>19.99</td>\n",
       "      <td>-2.11</td>\n",
       "      <td>9.85</td>\n",
       "      <td>-1.94</td>\n",
       "      <td>-3.38</td>\n",
       "      <td>...</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>无担保</td>\n",
       "      <td>2021-05-26 15:03</td>\n",
       "      <td>73</td>\n",
       "      <td>113527</td>\n",
       "      <td>维格转债</td>\n",
       "      <td>-10.7300</td>\n",
       "      <td>4.044400</td>\n",
       "      <td>603518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>113559</td>\n",
       "      <td>永创转债</td>\n",
       "      <td>141.120</td>\n",
       "      <td>永创智能</td>\n",
       "      <td>603901</td>\n",
       "      <td>14.73</td>\n",
       "      <td>0.07</td>\n",
       "      <td>10.32</td>\n",
       "      <td>-1.13</td>\n",
       "      <td>0.08</td>\n",
       "      <td>...</td>\n",
       "      <td>X</td>\n",
       "      <td>None</td>\n",
       "      <td>股份质押和保证</td>\n",
       "      <td>2021-05-26 15:03</td>\n",
       "      <td>89</td>\n",
       "      <td>113559</td>\n",
       "      <td>永创转债</td>\n",
       "      <td>31.5610</td>\n",
       "      <td>4.627650</td>\n",
       "      <td>603901</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    可转债代码 可转债名称    可转债价格  正股名称    正股代码   正股现价  正股涨跌幅  最新转股价   溢价率  可转债涨幅  ...  \\\n",
       "0  127029  中钢转债  169.593  中钢国际  000928  11.02  -1.78   5.89 -9.36  -0.91  ...   \n",
       "1  113541  荣晟转债  132.370  荣晟环保  603165  14.80   8.90  10.84 -3.05   5.70  ...   \n",
       "2  128085  鸿达转债  107.240  鸿达兴业  002002   4.31   9.67   3.92 -2.46   5.92  ...   \n",
       "3  113527  维格转债  199.000  锦泓集团  603518  19.99  -2.11   9.85 -1.94  -3.38  ...   \n",
       "4  113559  永创转债  141.120  永创智能  603901  14.73   0.07  10.32 -1.13   0.08  ...   \n",
       "\n",
       "   发出强赎公告  强赎日期       担保              更新日期  Unnamed: 0    bond  name       pe  \\\n",
       "0       X  None        无  2021-05-26 15:03         260  127029  中钢转债  19.6973   \n",
       "1       X  None  股份质押和保证  2021-05-26 15:03          81  113541  荣晟转债  13.8036   \n",
       "2       X  None      无担保  2021-05-26 15:03         313  128085  鸿达转债  12.8037   \n",
       "3       X  None      无担保  2021-05-26 15:03          73  113527  维格转债 -10.7300   \n",
       "4       X  None  股份质押和保证  2021-05-26 15:03          89  113559  永创转债  31.5610   \n",
       "\n",
       "         roe  zg_code  \n",
       "0   6.024312      928  \n",
       "1  11.290425   603165  \n",
       "2   6.940487     2002  \n",
       "3   4.044400   603518  \n",
       "4   4.627650   603901  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_bond = df3[['可转债代码','可转债名称','正股名称','正股代码','溢价率','可转债价格','pe','roe','担保']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>可转债代码</th>\n",
       "      <th>可转债名称</th>\n",
       "      <th>正股名称</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>溢价率</th>\n",
       "      <th>pe</th>\n",
       "      <th>roe</th>\n",
       "      <th>担保</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>127029</td>\n",
       "      <td>中钢转债</td>\n",
       "      <td>中钢国际</td>\n",
       "      <td>000928</td>\n",
       "      <td>-9.36</td>\n",
       "      <td>19.6973</td>\n",
       "      <td>6.024312</td>\n",
       "      <td>无</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>113541</td>\n",
       "      <td>荣晟转债</td>\n",
       "      <td>荣晟环保</td>\n",
       "      <td>603165</td>\n",
       "      <td>-3.05</td>\n",
       "      <td>13.8036</td>\n",
       "      <td>11.290425</td>\n",
       "      <td>股份质押和保证</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>128085</td>\n",
       "      <td>鸿达转债</td>\n",
       "      <td>鸿达兴业</td>\n",
       "      <td>002002</td>\n",
       "      <td>-2.46</td>\n",
       "      <td>12.8037</td>\n",
       "      <td>6.940487</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>113527</td>\n",
       "      <td>维格转债</td>\n",
       "      <td>锦泓集团</td>\n",
       "      <td>603518</td>\n",
       "      <td>-1.94</td>\n",
       "      <td>-10.7300</td>\n",
       "      <td>4.044400</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>113559</td>\n",
       "      <td>永创转债</td>\n",
       "      <td>永创智能</td>\n",
       "      <td>603901</td>\n",
       "      <td>-1.13</td>\n",
       "      <td>31.5610</td>\n",
       "      <td>4.627650</td>\n",
       "      <td>股份质押和保证</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    可转债代码 可转债名称  正股名称    正股代码   溢价率       pe        roe       担保\n",
       "0  127029  中钢转债  中钢国际  000928 -9.36  19.6973   6.024312        无\n",
       "1  113541  荣晟转债  荣晟环保  603165 -3.05  13.8036  11.290425  股份质押和保证\n",
       "2  128085  鸿达转债  鸿达兴业  002002 -2.46  12.8037   6.940487      无担保\n",
       "3  113527  维格转债  锦泓集团  603518 -1.94 -10.7300   4.044400      无担保\n",
       "4  113559  永创转债  永创智能  603901 -1.13  31.5610   4.627650  股份质押和保证"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bond.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-23-5461a34637e3>:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_bond['pe']=df_bond['pe'].astype(np.float64)\n",
      "<ipython-input-23-5461a34637e3>:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_bond['roe']=df_bond['roe'].astype(np.float64)\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "df_bond['pe']=df_bond['pe'].astype(np.float64)\n",
    "df_bond['roe']=df_bond['roe'].astype(np.float64)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>可转债代码</th>\n",
       "      <th>可转债名称</th>\n",
       "      <th>正股名称</th>\n",
       "      <th>正股代码</th>\n",
       "      <th>溢价率</th>\n",
       "      <th>可转债价格</th>\n",
       "      <th>pe</th>\n",
       "      <th>roe</th>\n",
       "      <th>担保</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>110038</td>\n",
       "      <td>济川转债</td>\n",
       "      <td>济川药业</td>\n",
       "      <td>600566</td>\n",
       "      <td>31.48</td>\n",
       "      <td>106.200</td>\n",
       "      <td>13.6487</td>\n",
       "      <td>19.987800</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>127023</td>\n",
       "      <td>华菱转2</td>\n",
       "      <td>华菱钢铁</td>\n",
       "      <td>000932</td>\n",
       "      <td>4.06</td>\n",
       "      <td>143.000</td>\n",
       "      <td>5.7230</td>\n",
       "      <td>19.917738</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>110045</td>\n",
       "      <td>海澜转债</td>\n",
       "      <td>海澜之家</td>\n",
       "      <td>600398</td>\n",
       "      <td>11.49</td>\n",
       "      <td>109.510</td>\n",
       "      <td>13.5049</td>\n",
       "      <td>18.056487</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>123039</td>\n",
       "      <td>开润转债</td>\n",
       "      <td>开润股份</td>\n",
       "      <td>300577</td>\n",
       "      <td>45.13</td>\n",
       "      <td>107.501</td>\n",
       "      <td>56.4536</td>\n",
       "      <td>17.951825</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>127017</td>\n",
       "      <td>万青转债</td>\n",
       "      <td>万年青</td>\n",
       "      <td>000789</td>\n",
       "      <td>19.00</td>\n",
       "      <td>118.160</td>\n",
       "      <td>7.2743</td>\n",
       "      <td>17.575463</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>128041</td>\n",
       "      <td>盛路转债</td>\n",
       "      <td>盛路通信</td>\n",
       "      <td>002446</td>\n",
       "      <td>238.18</td>\n",
       "      <td>291.582</td>\n",
       "      <td>24.7298</td>\n",
       "      <td>-0.544175</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>319</th>\n",
       "      <td>128023</td>\n",
       "      <td>亚太转债</td>\n",
       "      <td>亚太股份</td>\n",
       "      <td>002284</td>\n",
       "      <td>53.58</td>\n",
       "      <td>104.267</td>\n",
       "      <td>81.7978</td>\n",
       "      <td>-0.567812</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>279</th>\n",
       "      <td>128036</td>\n",
       "      <td>金农转债</td>\n",
       "      <td>金新农</td>\n",
       "      <td>002548</td>\n",
       "      <td>40.02</td>\n",
       "      <td>120.907</td>\n",
       "      <td>49.6904</td>\n",
       "      <td>-2.104125</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>128015</td>\n",
       "      <td>久其转债</td>\n",
       "      <td>久其软件</td>\n",
       "      <td>002279</td>\n",
       "      <td>38.45</td>\n",
       "      <td>103.490</td>\n",
       "      <td>33.5197</td>\n",
       "      <td>-8.805338</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>128062</td>\n",
       "      <td>亚药转债</td>\n",
       "      <td>亚太药业</td>\n",
       "      <td>002370</td>\n",
       "      <td>207.81</td>\n",
       "      <td>78.800</td>\n",
       "      <td>75.8917</td>\n",
       "      <td>-34.779625</td>\n",
       "      <td>无担保</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>362 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      可转债代码 可转债名称  正股名称    正股代码     溢价率    可转债价格       pe        roe   担保\n",
       "244  110038  济川转债  济川药业  600566   31.48  106.200  13.6487  19.987800  无担保\n",
       "44   127023  华菱转2  华菱钢铁  000932    4.06  143.000   5.7230  19.917738  无担保\n",
       "103  110045  海澜转债  海澜之家  600398   11.49  109.510  13.5049  18.056487  无担保\n",
       "298  123039  开润转债  开润股份  300577   45.13  107.501  56.4536  17.951825  无担保\n",
       "166  127017  万青转债   万年青  000789   19.00  118.160   7.2743  17.575463  无担保\n",
       "..      ...   ...   ...     ...     ...      ...      ...        ...  ...\n",
       "359  128041  盛路转债  盛路通信  002446  238.18  291.582  24.7298  -0.544175  无担保\n",
       "319  128023  亚太转债  亚太股份  002284   53.58  104.267  81.7978  -0.567812  无担保\n",
       "279  128036  金农转债   金新农  002548   40.02  120.907  49.6904  -2.104125  无担保\n",
       "268  128015  久其转债  久其软件  002279   38.45  103.490  33.5197  -8.805338  无担保\n",
       "358  128062  亚药转债  亚太药业  002370  207.81   78.800  75.8917 -34.779625  无担保\n",
       "\n",
       "[362 rows x 9 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bond.sort_values(by='roe',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19.9878"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['roe'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_bond.to_excel('bond_roe.xlsx',encoding='utf8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
